import pymysql

# 建立数据库连接
from settings import Config


def connection():
    db = pymysql.connect(host="10.0.12.72",
                         port=3306,
                         user='root',
                         password='Cosmo_6003',
                         database='mxwi')
    cursor = db.cursor()
    return cursor


# 根据role_id查询home_role中数据
def home_role_select(role_id):
    cursor = connection()
    sql = "select distinct menu_id from home_role where role_id=" + str(role_id) + ";"
    cursor.execute(sql)
    role = cursor.fetchall()
    cursor.close()
    if role:
        return role
    else:
        return None


def menu_select(role_id):
    cursor = connection()
    sql = "select distinct b.one_menu,b.two_menu,b.url,b.homepage_title,b.homepage_id from " \
          "home_role a, " \
          "homepage_details b " \
          "where " \
          "a.menu_id=b.homepage_id " \
          "and a.role_id=" + str(role_id) + ";"
    cursor.execute(sql)
    menu = cursor.fetchall()
    if menu:
        return menu
    else:
        return None


# 在user_role_map,users,role表里查询role_id
def role_id_select(user_id):
    cursor = connection()
    sql = "select c.role_id from " \
          "user_role_map a," \
          "users b," \
          "role c " \
          "where " \
          "a.person_id=b.userid " \
          "and a.role_id=c.role_id " \
          "and c.memo='1' " \
          "and b.userid=" + str(user_id) + ";"
    cursor.execute(sql)
    role_id = cursor.fetchone()
    cursor.close()
    if role_id:
        return str(role_id[0])
    else:
        return None


# 根据user_id查询home_user表,返回list类型
def home_user_select(user_id):
    cursor = connection()
    sql = "select menu_id from home_user where user_id='" + str(user_id) + "' order by number desc;"
    print(sql)
    cursor.execute(sql)
    user = cursor.fetchall()
    user_list = []
    for line in user:
        user_list.append(line[0])
    cursor.close()
    if user:
        return user_list
    else:
        return None


# 根据menu_id查询homepage_detail中的菜单信息
def homepage_detail_select(user_id):
    cursor = connection()
    sql = "select " \
          "b.one_menu,b.two_menu,b.url,b.homepage_title,b.homepage_id " \
          "from " \
          "home_user a left join homepage_details b " \
          "on " \
          "a.menu_id=b.homepage_id " \
          "where " \
          "a.user_id=" + str(user_id) + " order by a.number;"
    cursor.execute(sql)
    print(sql)
    menu = cursor.fetchall()
    cursor.close()
    if menu:
        return menu
    else:
        return None

# 根据menu_id查询homepage_detail中的菜单信息
# def homepage_detail_select(menu_id):
#     homepage_id = ""
#     for line in menu_id:
#         homepage_id += str(line) + ","
#     homepage_id = homepage_id[:-1]
#     homepage_id = "(" + homepage_id + ")"
#     cursor = connection()
#     sql = "select one_menu,two_menu,url,homepage_title from homepage_details where homepage_id in" + homepage_id + ";"
#     cursor.execute(sql)
#     print(sql)
#     menu = cursor.fetchall()
#     cursor.close()
#     if menu:
#         return menu
#     else:
#         return None
